﻿using JNPF.Data;
using JNPF.Data.Util;
using JNPF.Saas.Models.BaseTenant.Entities;
using JNPF.Utils;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace JNPF.Saas.Logic.Tenant
{
    /// <summary>
    /// 租户信息
    /// 版 本：V3.0.0
    /// 版 权：引迈信息技术有限公司（https://www.jnpfsoft.com）
    /// 作 者：JNPF开发平台组
    /// 日 期：2020.12.21 
    /// </summary>
    internal class TenantService : DataFactory, TenantIService
    {
        /// <summary>
        /// 列表（分页）
        /// </summary>
        /// <param name="input">参数</param>
        /// <returns></returns>
        public async Task<PageResult<TenantEntity>> GetList(PageInput input)
        {
            using (var db = this.Basic)
            {
                var queryWhere = ExtLinq.True<TenantEntity>();
                var queryParam = input.queryJson.ToObject();
                if (!input.keyword.IsEmpty())
                {
                    var keyword = input.keyword.ToString();
                    queryWhere = queryWhere.And(m => m.FullName.Contains(keyword) || m.EnCode.Contains(keyword) || m.CompanyName.Contains(keyword));
                }
                if (!queryParam["startTime"].IsEmpty() && !queryParam["endTime"].IsEmpty())
                {
                    var startTime = CommonHelper.GetDateTime(queryParam["startTime"].ToString());
                    var endTime = CommonHelper.GetDateTime(queryParam["endTime"].ToString());
                    startTime = new DateTime(startTime.Year, startTime.Month, startTime.Day, 0, 0, 0, 0);
                    endTime = new DateTime(endTime.Year, endTime.Month, endTime.Day, 23, 59, 59, 999);
                    queryWhere = queryWhere.And(t => t.CreatorTime >= startTime && t.CreatorTime <= endTime);
                }
                return await db.GetIQueryable<TenantEntity>().Where(queryWhere).OrderByDescending(t => t.CreatorTime).GetPageResultAsync(input);
            }
        }
        /// <summary>
        /// 列表
        /// </summary>
        /// <returns></returns>
        public async Task<List<TenantEntity>> GetList()
        {
            using (var db = this.Basic)
            {
                return await db.GetListAsync<TenantEntity>();
            }
        }
        /// <summary>
        /// 信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<TenantEntity> GetInfo(string id)
        {
            using (var db = this.Basic)
            {
                return await db.GetEntityAsync<TenantEntity>(x => x.Id == id);
            }
        }
        /// <summary>
        /// 获取租户数据库
        /// </summary>
        /// <param name="encode"></param>
        /// <returns></returns>
        public async Task<TenantEntity> GetDbContent(string encode)
        {
            using (var db = this.Basic)
            {
                return await db.GetEntityAsync<TenantEntity>(x => x.EnCode == encode);
            }
        }
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task<int> Create(TenantEntity entity)
        {
            using (var db = this.Basic)
            {
                return await db.InsertAsync(entity);
            }
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> Delete(TenantEntity entity)
        {
            using (var db = this.Basic)
            {
                return await db.DeleteAsync(entity);
            }
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task<int> Update(TenantEntity entity)
        {
            using (var db = this.Basic)
            {
                return await db.UpdateAsync(entity);
            }
        }
        /// <summary>
        /// 创建租户数据库
        /// </summary>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public async Task CreateDB(string dbName)
        {
            var dbinitName = Appsettings.app(new string[] { "JNPF_DB_init", "DBName" });
            var dbinitConnString = ConfigurationHelper.GetDbConnection("JNPF_DB_init:ConnectionString");
            var dbinitType = ConfigurationHelper.GetDbConnection("JNPF_DB_init:ConnectionType").ToDatabaseType();
            var db = this.Data(dbinitConnString, dbinitType);

            #region 创建数据库
            var CrDataBaseSql = "CREATE DATABASE " + dbName;
            await db.ExecuteSqlAsync(CrDataBaseSql);
            #endregion
            #region 初始化数据库
            var strSql = DBTableSql(dbinitType);
            var data = await db.GetListSqlAsync<DbTableModel>(strSql);
            StringBuilder sb = new StringBuilder();
            if (dbinitType.Equals(DatabaseType.MySql))
            {
                foreach (var item in data)
                {
                    sb.AppendFormat(@"create table {0}.{1} as select * from  {2};", dbName, item.Table, item.Table, dbinitName);
                }
            }
            else
            {
                foreach (var item in data)
                {
                    sb.AppendFormat(@"select * into {0}.dbo.{1} from {2}.dbo.{3};", dbName, item.Table, dbinitName, item.Table);
                }
            }
            await db.ExecuteSqlAsync(sb.ToString());
            #endregion
        }
        /// <summary>
        /// 删除租户数据库
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task DeleteDB(TenantEntity entity)
        {
            var dbinitConnString = ConfigurationHelper.GetDbConnection("JNPF_DB_init:ConnectionString");
            var dbinitType = ConfigurationHelper.GetDbConnection("JNPF_DB_init:ConnectionType").ToDatabaseType();
            var dbinit = this.Data(dbinitConnString, dbinitType);
            var exists = (await dbinit.ExecuteScalarAsync(string.Format("select count(*) from sys.databases where name='{0}'", entity.DbName))).ToInt();
            if (exists == 1)
            {
                await dbinit.ExecuteSqlAsync(string.Format("ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", entity.DbName));
                await dbinit.ExecuteSqlAsync(string.Format("DROP DATABASE {0}", entity.DbName));
            }
        }

        /// <summary>
        /// 数据库表SQL
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <returns></returns>
        public static string DBTableSql(DatabaseType dbType)
        {
            StringBuilder sb = new StringBuilder();
            switch (dbType)
            {
                case DatabaseType.SqlServer:
                    sb.Append(@"DECLARE @TABLEINFO TABLE ( NAME VARCHAR(50) , SUMROWS VARCHAR(11) , RESERVED VARCHAR(50) , DATA VARCHAR(50) , INDEX_SIZE VARCHAR(50) , UNUSED VARCHAR(50) , PK VARCHAR(50) ) DECLARE @TABLENAME TABLE ( NAME VARCHAR(50) ) DECLARE @NAME VARCHAR(50) DECLARE @PK VARCHAR(50) INSERT INTO @TABLENAME ( NAME ) SELECT O.NAME FROM SYSOBJECTS O , SYSINDEXES I WHERE O.ID = I.ID AND O.XTYPE = 'U' AND I.INDID < 2 ORDER BY I.ROWS DESC , O.NAME WHILE EXISTS ( SELECT 1 FROM @TABLENAME ) BEGIN SELECT TOP 1 @NAME = NAME FROM @TABLENAME DELETE @TABLENAME WHERE NAME = @NAME DECLARE @OBJECTID INT SET @OBJECTID = OBJECT_ID(@NAME) SELECT @PK = COL_NAME(@OBJECTID, COLID) FROM SYSOBJECTS AS O INNER JOIN SYSINDEXES AS I ON I.NAME = O.NAME INNER JOIN SYSINDEXKEYS AS K ON K.INDID = I.INDID WHERE O.XTYPE = 'PK' AND PARENT_OBJ = @OBJECTID AND K.ID = @OBJECTID INSERT INTO @TABLEINFO ( NAME , SUMROWS , RESERVED , DATA , INDEX_SIZE , UNUSED ) EXEC SYS.SP_SPACEUSED @NAME UPDATE @TABLEINFO SET PK = @PK WHERE NAME = @NAME END SELECT F.NAME AS F_TABLE,ISNULL(P.TDESCRIPTION,F.NAME) AS F_TABLENAME, F.RESERVED AS F_SIZE, RTRIM(F.SUMROWS) AS F_SUM, F.PK AS F_PRIMARYKEY FROM @TABLEINFO F LEFT JOIN ( SELECT NAME = CASE WHEN A.COLORDER = 1 THEN D.NAME ELSE '' END , TDESCRIPTION = CASE WHEN A.COLORDER = 1 THEN ISNULL(F.VALUE, '') ELSE '' END FROM SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE = B.XUSERTYPE INNER JOIN SYSOBJECTS D ON A.ID = D.ID AND D.XTYPE = 'U' AND D.NAME <> 'DTPROPERTIES' LEFT JOIN SYS.EXTENDED_PROPERTIES F ON D.ID = F.MAJOR_ID WHERE A.COLORDER = 1 AND F.MINOR_ID = 0 ) P ON F.NAME = P.NAME WHERE 1 = 1 ORDER BY F_TABLE");
                    break;
                case DatabaseType.Oracle:
                    sb.Append(@"SELECT DISTINCT COL.TABLE_NAME AS F_TABLE,TAB.COMMENTS AS F_TABLENAME,0 AS F_SIZE,NVL(T.NUM_ROWS,0)AS F_SUM,COLUMN_NAME AS F_PRIMARYKEY FROM USER_CONS_COLUMNS COL INNER JOIN USER_CONSTRAINTS CON ON CON.CONSTRAINT_NAME=COL.CONSTRAINT_NAME INNER JOIN USER_TAB_COMMENTS TAB ON TAB.TABLE_NAME=COL.TABLE_NAME INNER JOIN USER_TABLES T ON T.TABLE_NAME=COL.TABLE_NAME WHERE CON.CONSTRAINT_TYPE NOT IN('C','R')ORDER BY COL.TABLE_NAME");
                    break;
                case DatabaseType.MySql:
                    sb.Append(@"SELECT T1.*,(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.`COLUMNS`WHERE TABLE_SCHEMA=DATABASE()AND TABLE_NAME=T1.F_TABLE AND COLUMN_KEY='PRI')F_PRIMARYKEY FROM(SELECT TABLE_NAME F_TABLE,0 F_SIZE,TABLE_ROWS F_SUM,(SELECT IF(LENGTH(TRIM(TABLE_COMMENT))<1,TABLE_NAME,TABLE_COMMENT))F_TABLENAME FROM INFORMATION_SCHEMA.`TABLES`WHERE TABLE_SCHEMA=DATABASE())T1 ORDER BY T1.F_TABLE");
                    break;
                default:
                    throw new Exception("不支持");
            }
            return sb.ToString();
        }

    }
}
